﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ServiceModel;
using System.ServiceModel.Activation;
using System.Data;
using System.Data.OracleClient;
using System.IO;

namespace VAdvantageSvc
{

    [ServiceContract(Namespace = "VAdvantageSvc")]
    public interface IDataServicesTcp
    {

        [OperationContract]
        DataSetData GetDataObject(string sql, int pageNumber, int pageSize, out CustomException ServiceError);
        [OperationContract]
        String SetDataObject();

        [OperationContract]
        String GetDataXML(string sql, out CustomException ServiceError);

        [OperationContract]
        String GetDataXMLPaging(string sql, int start, int end, bool? state, string tableName);


        [OperationContract]
        String SetDataXML(string xml);

        [OperationContract]
        String SetDataLobXML(string xml, string pName, Object value);

        [OperationContract]
        String GetScalar(string xml);

        [OperationContract]
        bool CheckViennaService();

        [OperationContract]
        List<String> ExecuteMultipleQueries(List<String> sqlList, out CustomException ServiceError); 


        [OperationContract]
        Byte[] GetDataBytes(string sql);


    }


   
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
    [ServiceBehavior(ConcurrencyMode = ConcurrencyMode.Multiple,InstanceContextMode= InstanceContextMode.Single )]

    public class DataServicesTcp:IDataServicesTcp
    {

        private string connectionString = System.Configuration.ConfigurationSettings.AppSettings["oracleConnectionString"];


        #region "DataObject"


        public DataSetData GetDataObject(string sql, out CustomException error)
        {
            return GetDataObject(sql, 0, 0, out  error);
        }
        public DataSetData GetDataObject(string sql, int pageNumber, int pageSize, out CustomException ServiceError)
        {
            try
            {
                DataSet ds = GetDataSet(sql, pageNumber, pageSize);
                ServiceError = null;

                DataSetData ds1 = DataSetData.FromDataSet(ds);

                return ds1;
            }
            catch (Exception ex)
            {
                ServiceError = new CustomException(ex);
            }
            return null;
        }

        private DataSet GetDataSet(string SQL, int PageNumber, int PageSize)
        {
            DataSet ds;
            OracleConnection Connection = new OracleConnection(connectionString);
            try
            {
                //int indexOrder = SQL.IndexOf("ORDER");
                //if (indexOrder > -1)
                //{
                //    SQL = SQL.Substring(0, indexOrder) + " AND RowNum < 51 " + SQL.Substring(indexOrder); 
                //}


                Connection.Open();
                OracleDataAdapter adapter = new OracleDataAdapter();
                adapter.SelectCommand = new OracleCommand(SQL);
                adapter.SelectCommand.Connection = Connection;
                if (PageSize > 0)// Set rowcount =PageNumber * PageSize for best performance
                {
                    //long RowCount = PageNumber * PageSize;
                    //OracleCommand cmd = new OracleCommand("SET ROWCOUNT " + RowCount.ToString() + " SET NO_BROWSETABLE ON", (OracleConnection)Connection);
                    //cmd.ExecuteNonQuery();
                }
                ds = new DataSet();
                adapter.Fill(ds, (PageNumber - 1) * PageSize, PageSize, "Data");
                adapter.FillSchema(ds, SchemaType.Mapped, "DataSchema");
                if (PageSize > 0) // Reset Rowcount back to 0
                {
                    //OracleCommand cmd = new OracleCommand("SET ROWCOUNT 0 SET NO_BROWSETABLE OFF", (OracleConnection)Connection);
                    //cmd.ExecuteNonQuery();
                }
                if (ds.Tables.Count > 1)
                {
                    DataTable data = ds.Tables["Data"];
                    DataTable schema = ds.Tables["DataSchema"];
                    data.Merge(schema);
                    ds.Tables.Remove(schema);
                }
            }
            catch (Exception err)
            {
                throw err;
            }
            finally
            {
                Connection.Close();
            }
            return ds;
        }

        public string SetDataObject()
        {
            throw new NotImplementedException();
        }

        #endregion  "DataObject"

        #region "XmlString"

        public string SetDataLobXML(string sql, string pName, Object value)
        {
            OracleConnection Connection = new OracleConnection(connectionString);
            int i = -1;
            try
            {
                Connection.Open();

                OracleCommand cmd = new OracleCommand(sql, Connection);

                if (value is byte[])
                    cmd.Parameters.AddWithValue(pName, (byte[])value);
                else
                    cmd.Parameters.AddWithValue(pName, (string)value);

                cmd.CommandType = CommandType.Text;
                i = cmd.ExecuteNonQuery();
            }
            catch
            {
                return "-1";
            }
            finally
            {
                Connection.Close();
            }
            return i.ToString();
        }


        public string SetDataXML(string sql)
        {
            OracleConnection Connection = new OracleConnection(connectionString);
            int i = -1;
            try
            {
                Connection.Open();

                OracleCommand cmd = new OracleCommand(sql, Connection);
                cmd.CommandType = CommandType.Text;
                i = cmd.ExecuteNonQuery();
            }
            catch
            {

            }
            finally
            {
                Connection.Close();
            }
            return i.ToString();
        }

        #endregion

        public string GetScalar(string sql)
        {
            OracleConnection Connection = new OracleConnection(connectionString);
            object i;
            try
            {
                Connection.Open();

                OracleCommand cmd = new OracleCommand(sql, Connection);
                cmd.CommandType = CommandType.Text;
                i = cmd.ExecuteScalar();
            }
            catch (Exception e)
            {
                throw new FaultException(e.Message);
                return null;
            }
            finally
            {
                Connection.Close();
            }
            return (i == null || i == DBNull.Value) ? null : i.ToString();
        }

        public string GetDataXML(string sql, out CustomException error)
        {
            DataSet ds;
            OracleConnection Connection = new OracleConnection(connectionString);
            try
            {
                Connection.Open();
                OracleDataAdapter adapter = new OracleDataAdapter();
                adapter.SelectCommand = new OracleCommand(sql);
                adapter.SelectCommand.Connection = Connection;
                //if (PageSize > 0)// Set rowcount =PageNumber * PageSize for best performance
                //{
                //    long RowCount = PageNumber * PageSize;
                //    OracleCommand cmd = new OracleCommand('SET ROWCOUNT ' + RowCount.ToString() + ' SET NO_BROWSETABLE ON', (OracleConnection)Connection);
                //    cmd.ExecuteNonQuery();
                //}
                ds = new DataSet();
                adapter.Fill(ds);//, 'Data');// (PageNumber - 1) * PageSize, PageSize, 'Data');
                //adapter.FillSchema(ds);//, SchemaType.Mapped, 'DataSchema');
                //if (PageSize > 0) // Reset Rowcount back to 0
                //{
                //    OracleCommand cmd = new OracleCommand('SET ROWCOUNT 0 SET NO_BROWSETABLE OFF', (OracleCommand)Connection);
                //    cmd.ExecuteNonQuery();
                //}
                if (ds.Tables.Count > 1)
                {
                    DataTable data = ds.Tables[0];
                    //DataTable schema = ds.Tables['DataSchema'];
                    // data.Merge(schema);
                    // ds.Tables.Remove(schema);
                }

                string schema = ds.GetXmlSchema().Replace("<?xml version=\"1.0\" encoding=\"utf-16\"?>", "<TestDataSet>");

                error = null;
                return schema + ds.GetXml() + " </TestDataSet>";

            }
            catch (Exception err)
            {
                error = new CustomException(err);
            }
            finally
            {
                Connection.Close();
            }
            return "";
        }

        public string GetDataXMLPaging(string sql, int start, int end, bool? state, String tableName)
        {
            DataSet ds;
            OracleConnection Connection = new OracleConnection(connectionString);

            try
            {

                OracleDataAdapter adapter = new OracleDataAdapter();

                string sqlFrom = sql.Substring(sql.IndexOf("FROM"));

                if (state == null)
                {
                    sql = "SELECT * FROM ( SELECT " + tableName + ".* , RowNum as rn " + sqlFrom + " ) WHERE rn >= " + start + " AND  rn < " + end;
                }
                else if ((bool)state)
                {
                    sql = "Select * FROM ( SELECT " + tableName + ".* , RowNum as rn " + sqlFrom + " ) WHERE rn < " + end;
                }

                else
                {
                    sql = "SELECT * FROM ( SELECT " + tableName + ".* , RowNum as rn " + sqlFrom + " )  WHERE rn >= " + start;
                }

                adapter.SelectCommand = new OracleCommand(sql);


                Connection.Open();

                adapter.SelectCommand.Connection = Connection;
                //if (PageSize > 0)// Set rowcount =PageNumber * PageSize for best performance
                //{
                //    long RowCount = PageNumber * PageSize;
                //    OracleCommand cmd = new OracleCommand('SET ROWCOUNT ' + RowCount.ToString() + ' SET NO_BROWSETABLE ON', (OracleConnection)Connection);
                //    cmd.ExecuteNonQuery();
                //}
                ds = new DataSet();
                adapter.Fill(ds);//, 'Data');// (PageNumber - 1) * PageSize, PageSize, 'Data');
                //adapter.FillSchema(ds);//, SchemaType.Mapped, 'DataSchema');
                //if (PageSize > 0) // Reset Rowcount back to 0
                //{
                //    OracleCommand cmd = new OracleCommand('SET ROWCOUNT 0 SET NO_BROWSETABLE OFF', (OracleCommand)Connection);
                //    cmd.ExecuteNonQuery();
                //}
                if (ds.Tables.Count > 1)
                {
                    DataTable data = ds.Tables[0];
                    //DataTable schema = ds.Tables['DataSchema'];
                    // data.Merge(schema);
                    // ds.Tables.Remove(schema);
                }
            }
            catch (Exception err)
            {
                throw new FaultException("Query ->" + sql + "/n" + err.Message);
            }
            finally
            {
                Connection.Close();
            }

            StringBuilder xml = new StringBuilder("");
            StringBuilder schema = new StringBuilder("");

            if (state == null)
            {
                //string schema = ds.GetXmlSchema().Replace("<?xml version=\"1.0\" encoding=\"utf-16\"?>", "<TestDataSet>");
                xml.Append(ds.GetXml());
                xml.Remove(0, 12);
                xml.Remove(xml.Length - 13, 13);
            }
            else if ((bool)state)
            {
                schema.Append(ds.GetXmlSchema().Replace("<?xml version=\"1.0\" encoding=\"utf-16\"?>", "<TestDataSet>"));
                xml.Append(ds.GetXml());
                xml.Remove(xml.Length - 13, 13);
                xml.Insert(0, schema.ToString());
            }
            else
            {
                xml.Append(ds.GetXml());
                xml.Remove(0, 12).Append("</TestDataSet>"); ;
            }
            return xml.ToString();
        }

        public bool CheckViennaService()
        {
            return true;
        }


        public List<string> ExecuteMultipleQueries(List<string> sqlList, out CustomException error)
        {
            OracleConnection Connection = new OracleConnection(connectionString);

            List<String> logs = new List<string>();
            error = null;
            try
            {
                Connection.Open();

                foreach (String sql in sqlList)
                {
                    try
                    {
                        OracleCommand cmd = new OracleCommand(sql, Connection);
                        cmd.CommandType = CommandType.Text;
                        cmd.ExecuteNonQuery();
                    }
                    catch
                    {
                        logs.Add(sql);
                    }
                }
            }
            catch (Exception e)
            {
                error = new CustomException(e);
            }
            finally
            {
                Connection.Close();
            }
            return logs;
        }



        public Byte[] GetDataBytes(string sql)
        {
            DataSet ds;
            OracleConnection Connection = new OracleConnection(connectionString);
            try
            {
                Connection.Open();
                OracleDataAdapter adapter = new OracleDataAdapter();
                adapter.SelectCommand = new OracleCommand(sql);
                adapter.SelectCommand.Connection = Connection;
                //if (PageSize > 0)// Set rowcount =PageNumber * PageSize for best performance
                //{
                //    long RowCount = PageNumber * PageSize;
                //    OracleCommand cmd = new OracleCommand('SET ROWCOUNT ' + RowCount.ToString() + ' SET NO_BROWSETABLE ON', (OracleConnection)Connection);
                //    cmd.ExecuteNonQuery();
                //}
                ds = new DataSet();
                adapter.Fill(ds);//, 'Data');// (PageNumber - 1) * PageSize, PageSize, 'Data');
                //adapter.FillSchema(ds);//, SchemaType.Mapped, 'DataSchema');
                //if (PageSize > 0) // Reset Rowcount back to 0
                //{
                //    OracleCommand cmd = new OracleCommand('SET ROWCOUNT 0 SET NO_BROWSETABLE OFF', (OracleCommand)Connection);
                //    cmd.ExecuteNonQuery();
                //}
                if (ds.Tables.Count > 1)
                {
                    DataTable data = ds.Tables[0];
                    //DataTable schema = ds.Tables['DataSchema'];
                    // data.Merge(schema);
                    // ds.Tables.Remove(schema);
                }
            }
            catch (Exception err)
            {
                throw new FaultException("Query ->" + sql + "/n" + err.Message);
            }
            finally
            {
                Connection.Close();
            }

            string schema = ds.GetXmlSchema().Replace("<?xml version=\"1.0\" encoding=\"utf-16\"?>", "<TestDataSet>");

            UnicodeEncoding d = new UnicodeEncoding();

            return d.GetBytes(schema + ds.GetXml() + " </TestDataSet>");

        }
    }

    

}
